#-*- encoding: utf-8 -*-
import win32com.client 
from Misc.Utils import *

from DataAccess.QueryBlp import *

from datetime import date,timedelta

from ReportTool.HldReport import *
from ReportTool.PnLReport import *
from ReportTool.NAVReport import *
from ReportTool.MktValReport import *
from ReportTool.TradeReport import *
from ReportTool.RealizedPnL import *
from ReportTool.OnshoreHldReport import *

from Config.ReportConfig import *		

def SY_Monthly_Report(ref_date):

	nvpu_crt = query_latest_NAV(ref_date, ['SC016_F'])
	nvpu_icpt = query_inception_NAV(['SC016_F'])
	nvpu_pre_1m = query_latest_NAV(end_of_mth(ref_date, -1), ['SC016_F'])
	nvpu_pre_3m = query_latest_NAV(end_of_mth(ref_date, -3), ['SC016_F'])
	nvpu_pre_6m = query_latest_NAV(end_of_mth(ref_date, -6), ['SC016_F'])
	nvpu_pre_12m = query_latest_NAV(end_of_mth(ref_date, -12), ['SC016_F'])
	#av_crt = query_post_AV(ref_date, ['SC016'])
	#av_pre_1m = query_post_AV(end_of_mth(ref_date, -1), ['SC016'])
	av_crt = query_latest_NAV(ref_date, ['SC016'], is_per_unit=False, is_official=False)
	av_pre_1m = query_latest_NAV(end_of_mth(ref_date, -1), ['SC016'], is_per_unit=False, is_official=False)
	mv_crt = query_Mkt_Val(ref_date, ref_date, ['SC016'])
	mv_pre_1m = query_Mkt_Val(end_of_mth(ref_date,-1), end_of_mth(ref_date,-1), ['SC016'])
	
	tplt_file = TPLT_PATH + 'SY Monthly Report.xls'
	xApp = win32com.client.Dispatch("Excel.Application")
	xBook = xApp.Workbooks.Open(tplt_file)
	xSht = xBook.Worksheets('Data')

	xSht.Range(xSht.Cells(3, 11), xSht.Cells(3, 12)).Value = nvpu_crt[0][1:]
	xSht.Range(xSht.Cells(4, 11), xSht.Cells(4, 12)).Value = nvpu_pre_1m[0][1:]
	xSht.Range(xSht.Cells(5, 11), xSht.Cells(5, 12)).Value = nvpu_pre_3m[0][1:]
	xSht.Range(xSht.Cells(6, 11), xSht.Cells(6, 12)).Value = nvpu_pre_6m[0][1:]
	xSht.Range(xSht.Cells(7, 11), xSht.Cells(7, 12)).Value = nvpu_pre_12m[0][1:]
	xSht.Range(xSht.Cells(8, 11), xSht.Cells(8, 12)).Value = nvpu_icpt[0][1:]
	xSht.Cells(3, 13).Formula = '=s_dq_close("000300.SH",$K$3,1)'
	xSht.Cells(4, 13).Formula = '=s_dq_close("000300.SH",$K$4,1)'
	xSht.Cells(5, 13).Formula = '=s_dq_close("000300.SH",$K$5,1)'
	xSht.Cells(6, 13).Formula = '=s_dq_close("000300.SH",$K$6,1)'
	xSht.Cells(7, 13).Formula = '=s_dq_close("000300.SH",$K$7,1)'
	xSht.Cells(8, 13).Formula = '=s_dq_close("000300.SH",$K$8,1)'
	
	xSht.Cells(8, 4).Value = av_pre_1m[0][1]
	xSht.Cells(8, 5).Value = av_crt[0][1]
	xSht.Cells(8, 6).Value = av_crt[0][1]/av_pre_1m[0][1] - 1
	xSht.Cells(9, 4).Value = nvpu_pre_1m[0][2]
	xSht.Cells(9, 5).Value = nvpu_crt[0][2]
	xSht.Cells(9, 6).Value = nvpu_crt[0][2]/nvpu_pre_1m[0][2] - 1
	xSht.Cells(11, 4).Value = mv_pre_1m[0][2]/av_pre_1m[0][1]
	xSht.Cells(11, 5).Value = mv_crt[0][2]/av_crt[0][1]
	xSht.Cells(11, 6).Value = mv_crt[0][2]/av_crt[0][1] - mv_pre_1m[0][2]/av_pre_1m[0][1]
	xSht.Cells(18, 4).Value = 1 - mv_pre_1m[0][2]/av_pre_1m[0][1]
	xSht.Cells(18, 5).Value = 1 - mv_crt[0][2]/av_crt[0][1]
	xSht.Cells(18, 6).Value = mv_pre_1m[0][2]/av_pre_1m[0][1] - mv_crt[0][2]/av_crt[0][1]
	
	rept_file = REPT_PATH + 'SY Monthly Report_''' + ref_date.strftime("%Y%m%d") + '.xls'
	xBook.SaveAs(rept_file)
	xBook.Close()
	del xApp